package com.zreal.generator.codegenerate.database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * 读取表
 * @author PJ
 */
public class ReadTable {

    public static List<Column> readTableColumn(String tableName) {

        String dbType = DataSource.dataSource.getDbType();
        String url = DataSource.dataSource.getUrl();
        List<Column> columntList = new ArrayList<>();

        Connection conn = null;
        Statement stmt = null;
        String sql = null;
        ResultSet rs = null;
        try {
            conn = DataSource.getConnection();
            stmt = conn.createStatement();
            if ("oracle".equalsIgnoreCase(dbType)) {
                sql = MessageFormat.format(" select colstable.column_name column_name, " +
                        "colstable.data_type data_type, commentstable.comments column_comment, " +
                        "colstable.Data_Precision column_precision, colstable.Data_Scale column_scale," +
                        "colstable.Char_Length,colstable.nullable,colstable.table_name from user_tab_cols colstable " +
                        " inner join user_col_comments commentstable  " +
                        "on colstable.column_name = commentstable.column_name " +
                        " where colstable.table_name = commentstable.table_name and " +
                        "colstable.table_name like {0}", TableConvert.getV(tableName.toUpperCase()));
            }

            if ("mysql".equalsIgnoreCase(dbType)) {
                String dbName = url.substring(url.lastIndexOf("/") + 1);
                if(dbName.contains("?")){
                    dbName = dbName.substring(0,dbName.indexOf("?"));
                }
                sql = MessageFormat
                        .format("select column_name,data_type,column_comment,numeric_precision," +
                                        "numeric_scale,character_maximum_length,is_nullable nullable,table_name,column_key from " +
                                        "information_schema.columns where table_name like {0} and table_schema = {1}",
                                TableConvert.getV(tableName), TableConvert.getV(dbName));
            }

            if ("sqlserver".equalsIgnoreCase(dbType)) {
                sql = MessageFormat
                        .format("select cast(a.name as varchar(50)) column_name,  " +
                                        "cast(b.name as varchar(50)) data_type,  cast(e.value as varchar(200)) " +
                                        "comment,  cast(ColumnProperty(a.object_id,a.Name,'''Precision''') as int) " +
                                        "num_precision,  cast(ColumnProperty(a.object_id,a.Name,'''Scale''') as int) " +
                                        "num_scale,  a.max_length,  (case when a.is_nullable=1 then '''y''' else '''n'''" +
                                        " end) nullable   from sys.columns a left join sys.types b on a.user_type_id=" +
                                        "b.user_type_id left join sys.objects c on a.object_id=c.object_id and c.type=" +
                                        "'''U''' left join sys.extended_properties e on e.major_id=c.object_id and " +
                                        "e.minor_id=a.column_id and e.class=1 where c.name={0}",
                                TableConvert.getV(tableName));
            }

            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                Column column = new Column();
                column.setFieldDbName(rs.getString(1));
                column.setFieldName(rs.getString(1).toLowerCase());
                column.setFieldType(TableConvert.formatDataType(rs.getString(2).toLowerCase(), rs.getString(4), rs.getString(5)));
                column.setFieldDBType(TableConvert.formatDBFieldType(rs.getString(2)));
                column.setFiledComment(rs.getString(3));
                column.setPrecision(rs.getString(4));
                column.setScale(rs.getString(5));
                column.setCharmaxLength(rs.getString(6));
                column.setNullable(TableConvert.getNullAble(rs.getString(7)));
                column.setTableName(rs.getString(8));
                if ("mysql".equalsIgnoreCase(dbType)) {
                    String pri = rs.getString(9);
                    column.setPrimaryKey("PRI".equalsIgnoreCase(pri));
                }
                columntList.add(column);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }

        return columntList;
    }
}
